/**
 * 
 */
package com.disys.database.migration;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;

import com.disys.database.connectivity.DBConnectionManager;

/**
 * @author Ubaid Raja
 * 
 */
public class DumpSQLServerToMysql {

	Statement selectStatement;
	ResultSet selectRs;
	Statement s2;
	ResultSet rs2;
	PreparedStatement psmtMySQL;

	public DumpSQLServerToMysql() {

	}

	public void doMigration() {

		String format = "yyyyMMdd";

		SimpleDateFormat dateFormat = new SimpleDateFormat(format);
		Date date = new Date();

		Calendar cal = Calendar.getInstance();
		//date = dateFormat.format(date);
		cal.setTime(date);
		int week = cal.get(Calendar.WEEK_OF_YEAR);
		System.out.println("Current week number is : "+week);
		
		Connection con, con2;
		con = DBConnectionManager.getConnection(DBConnectionManager.SQLSERVER);
		con2 = DBConnectionManager.getConnection(DBConnectionManager.MYSQL);
		String sql = "SELECT * FROM PS_LOCATION_TBL;";
		String mysql = "";
		
//		obj.setWotclocaddr_1(selectRs.getString("ADDRESS1"));
//		obj.setWotclocaddr_2(selectRs.getString("ADDRESS2"));
//		obj.setWotclocname(selectRs.getString("DESCR"));
//		obj.setWotcloccity_1(selectRs.getString("CITY"));
		
		if (con != null && con2 != null) {
			try {
				
				HashMap<String, Integer> sizeOfColumns = new HashMap<String, Integer>();
				ResultSet rsColumns = null;
				
				DatabaseMetaData meta = con2.getMetaData();
				rsColumns = meta.getColumns(null, null, "tx_140ERWOTCLOC", null);
				while (rsColumns.next()) {
					String columnName = rsColumns.getString("COLUMN_NAME");
					//System.out.println("column name=" + columnName);
					int size = rsColumns.getInt("COLUMN_SIZE");
					//System.out.println("size:" + size);
					sizeOfColumns.put(columnName, size);
				}
				
				selectStatement = con.createStatement();

				selectRs = selectStatement.executeQuery(sql);

				mysql = "INSERT INTO "+con2.getCatalog()+".tx_140erwotcloc (wotclocaddr_1, wotclocaddr_2, wotclocname,wotcloccity_1,finalized) "
						+ "VALUES (?, ?, ?,?,?);";

				con2.setAutoCommit(false);
				psmtMySQL = con2.prepareStatement(mysql);
				con2.setAutoCommit(true);
				Integer length = 0;
				while (selectRs.next()) {
					try {
						String wotclocaddr_1 = selectRs.getString("ADDRESS1");
						length = sizeOfColumns.get("wotclocaddr_1");
						if(wotclocaddr_1.length() > length){
							wotclocaddr_1 = wotclocaddr_1.substring(0,length);
						}
						psmtMySQL.setString(1, wotclocaddr_1);
						
						String wotclocaddr_2 = selectRs.getString("ADDRESS2");
						length = sizeOfColumns.get("wotclocaddr_2");
						if(wotclocaddr_2.length() > length){
							wotclocaddr_2 = wotclocaddr_2.substring(0,length);
						}
						psmtMySQL.setString(2, wotclocaddr_2);
						
						String wotclocname = selectRs.getString("DESCR");
						length = sizeOfColumns.get("wotclocname");
						if(wotclocname.length() > length){
							wotclocname = wotclocname.substring(0,length);
						}
						
						psmtMySQL.setString(3, wotclocname);
						
						
						String wotcloccity_1 = selectRs.getString("CITY");
						length = sizeOfColumns.get("wotcloccity_1");
						if(wotcloccity_1.length() > length){
							wotcloccity_1 = wotcloccity_1.substring(0,length);
						}
						
						psmtMySQL.setString(4, wotcloccity_1);
						
						psmtMySQL.setBoolean(5, false);
						psmtMySQL.addBatch();
					} catch (SQLException sqle) {
						sqle.printStackTrace();
					}
				}
				psmtMySQL.executeBatch();
				con2.setAutoCommit(true);
				psmtMySQL.close();
				//rs2.close();
				//s2.close();
				con2.close();
				selectRs.close();
				selectStatement.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static void main(String[] args) {
		DumpSQLServerToMysql obj = new DumpSQLServerToMysql();
		obj.doMigration();
	}

}
